package com.shigen.demo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shigen.demo.dao.UserMapper;
import com.shigen.demo.entity.User;
import com.shigen.demo.excel.vo.UserVo;
import com.shigen.demo.service.UserService;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.sql.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

/**
 * (SysUser)表服务实现类
 *
 * @author shigen
 * @since 2022-03-05 17:06:50
 */
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Resource
    private DataSource dataSource;

    @Override
    public Map<String, Object> importDBFromExcel10w(List<UserVo> dataList) {
        HashMap<String, Object> result = new HashMap<>();
        // 结果集中数据为0时,结束方法.进行下一次调用
        if (dataList.size() == 0) {
            result.put("empty", "0000");
            return result;
        }
        // JDBC分批插入+事务操作完成对10w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long startTime = System.currentTimeMillis();
            log.info(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");
            conn = dataSource.getConnection();
            // 控制事务:默认不提交
            conn.setAutoCommit(false);

            String sql = "insert into user (username, password, nickname, phone, introduction) values (?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
            // 循环结果集
            for (int i = 0; i < dataList.size(); i++) {
                UserVo user = dataList.get(i);
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getPassword());
                ps.setString(3, user.getNickname());
                ps.setString(4, user.getPhone());
                ps.setString(5, user.getIntroduction());

                // 将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            // 执行批处理
            ps.executeBatch();
            // 手动提交事务
            conn.commit();
            long endTime = System.currentTimeMillis();
            log.info(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");
            log.info(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");
            result.put("success", "1111");
        } catch (Exception e) {
            result.put("exception", "0000");
            e.printStackTrace();
        } finally {
            // 关连接
            try {
                assert ps != null;
                ps.close();
                conn.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        return result;
    }
}

